package com.wuseguang.springcloud.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.wuseguang.springcloud.Filter.RequestParameterWrapper;
import com.wuseguang.springcloud.JdbcTemplate.HaiweidaCooperativeSchoolJdbc;
import com.wuseguang.springcloud.dao.jdbc.ClassesJdbc;
import com.wuseguang.springcloud.dao.jdbc.StudentJdbc;
import com.wuseguang.springcloud.dao.mapper.StudentMapper;
import com.wuseguang.springcloud.dao.repository.ParentRepository;
import com.wuseguang.springcloud.dao.repository.ParentStudentRepository;
import com.wuseguang.springcloud.dao.repository.StudentRepository;
import com.wuseguang.springcloud.dao.repository.StudentRoleRepository;
import com.wuseguang.springcloud.entity.Parent;
import com.wuseguang.springcloud.entity.ParentStudent;
import com.wuseguang.springcloud.entity.Student;
import com.wuseguang.springcloud.entity.StudentRole;
import com.wuseguang.springcloud.service.StudentService;
import com.wuseguang.springcloud.util.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * <p>
 * 学生表 服务实现类
 * </p>
 *
 * @author
 * @since 2019-12-29
 */
@Service
public class StudentServiceImpl implements StudentService {

    private Logger logger = LoggerFactory.getLogger(this.getClass());
    @Autowired
    private StudentJdbc studentJdbc;
    @Autowired
    private StudentMapper studentMapper;
    @Autowired
    private StudentRepository studentRepository;
    @Autowired
    private StudentRoleRepository studentRoleRepository;
    @Autowired
    private ParentRepository parentRepository;
    @Autowired
    private ParentStudentRepository parentStudentRepository;
    @Autowired
    private ClassesJdbc classesJdbc;
    @Autowired
    private HaiweidaCooperativeSchoolJdbc haiweidaCooperativeSchoolJdbc;


    @Override
    public Result findAllStudent(Integer classId, String stuName, String schoolId, Integer indexNo, Integer rows, String teacherId, String admin, String phone) {

        logger.info("查看学校学生 StudentServiceImpl -> findAllStudent");
        //查询教师sql
        String sql = "select s.id,s.student_name,s.class_id ,s.sex,stu_no,s.phone,s.card_number,s.state,s.head_portrait ,c.`name`,DATE_FORMAT(s.admission_time,'%Y-%m-%d') admissionTime from student s LEFT JOIN classes c on(s.class_id=c.id) where s.school_id = ? and c.graduation=0 ";


        //判断班级id是否为空
        if (!StringUtils.isEmpty(classId)) {
            sql += " and `class_id` = '" + classId + "'";
        }

        if (!"0".equals(admin)) {
            sql += " and `class_id` in (" + studentJdbc.findClassIds(schoolId, teacherId) + ")";
        }

        if (!StringUtils.isEmpty(phone)) {
            sql += " and phone LIKE '%" + phone + "%' ";
        }

        //判断学生名字是否为空
        if (!StringUtils.isEmpty(stuName)) {
            sql += " and student_name LIKE '%" + stuName + "%' ";
        }


        logger.info("查询教师sql");

        List<Map<String, Object>> list = studentJdbc.queryListMap(sql, schoolId);


        //创建分页工具
        PagingTool tool = new PagingTool();
        //放入页码
        tool.setIndexNo(indexNo);
        //放入每页显示数量
        tool.setRows(rows);
        //放入角色集合
        tool.setList(list);

        //获取分页后的数据在查询
        List<Map<String, Object>> l = (List<Map<String, Object>>) tool.getList();
        //查询角色
        String rkSql = "select r.`name` from student_role sr  left join roles r on(r.id=sr.role_id) where sr.student_id = ?";

        //循环信息
        for (Map<String, Object> map : l) {
            List<Map<String, Object>> rklist = studentJdbc.queryListMap(rkSql, map.get("id"));
            map.put("list", rklist);
        }
        //返回数据
        return new Result(new PaginationUtil<>(list.size(), rows, l, indexNo));
    }

    @Override
    public Result deleteStudent(Integer id) {

        try {
            //查询学生人脸是否已注册
            String sql = "select COUNT(1) count  from student_faces where student_id = ? and (state =2 or state = 5)";
            Integer count = studentJdbc.queryCount(sql, id);
            if (count > 0) {
                return new Result(CodeUtil.STUDENT_FACE_REGISTERED);
            }

            logger.info("删除学生信息");
            studentRepository.deleteById(id);
            ParentStudent ps = parentStudentRepository.findByStudentId(id);
            parentStudentRepository.deleteById(ps.getId());
            parentRepository.deleteById(ps.getParentId());
        } catch (Exception e) {

        }
        return new Result();
    }

    @Override
    public Result addStudent(Student student) {

        logger.info("添加学生");

        //循环每个学生的角色
        List<String> studentRoleList = (List<String>) JSON.parse(student.getList());

        //查询这个学校有的角色
        String sql = "select name,id from `roles` WHERE `school_id` = ? and `type` = 2";
        logger.info("询这个学校有的角色 SQL ={}", sql);
        Map<String, Object> roleMap = studentJdbc.queryIdMap(sql, "name", "id", student.getSchoolId());
        logger.info("学校有的角色map大小 = {}", roleMap.size());

        //查询这个学校的班级
        sql = "select c.id,c.`name`   from `classes` c  WHERE c.`school_id`  = ?";
        logger.info("查询这个学校的班级 SQL ={}", sql);
        Map<String, Object> classMap = studentJdbc.queryIdMap(sql, "name", "id", student.getSchoolId());
        logger.info("班级map大小 = {}", classMap.size());

        logger.info("添加学生信息");
        //班级id
        student.setClassId(Integer.valueOf(classMap.get(studentRoleList.get(0).split("-")[0]).toString()));
        student.setState(0);

        if (StringUtils.isEmpty(student.getHeadPortrait())) {
            //头像
            student.setHeadPortrait(student.getSex() == 0 ? "https://oss.yuguozhijia.online/headPortrait/学生头像（男）.png" : "https://oss.yuguozhijia.online/headPortrait/学生头像（女）.png");
        }

        //验证结果
        int verify = verifyPhone(student);
        if (verify == 1) {
            return new Result(CodeUtil.PLEASE_ENTER_THE_CORRECT_MOBILE_PHONE_NUMBER);
        } else if (verify == 2) {
            return new Result(CodeUtil.MOBILE_NUMBER_ALREADY_EXISTS);
        }


        sql = "select card_number,`id` from student where `school_id`  = " + student.getSchoolId();
        Map<String, String> phoneMap = studentJdbc.getMap(sql, "card_number", "id");

        //卡号验证结果
        int verifyCard = verifyCard(student.getCardNumber(), phoneMap, null);
        if (verifyCard == 1) {
            return new Result(CodeUtil.THE_CARD_NUMBER_TO_REPEAT);
        }

        Integer stuId = studentRepository.save(student).getId();
        logger.info("学生id = {}", stuId);
        try {
            List<Student> hwdlist = new ArrayList<>();
            hwdlist.add(student);
            haiweidaCooperativeSchoolJdbc.haiweidaStudent(hwdlist);
        } catch (Exception e) {
            logger.info("海威达同步学生失败 e", e);
        }

        //创建家长信息
        Parent p = new Parent();
        p.setName(student.getStudentName() + "家长");
        p.setPassword("x16YjoF1LNE=");
        p.setPhone(student.getPhone());
        Integer pId = parentRepository.save(p).getId();
        logger.info("家长id");

        //创建家长跟学生的实体
        ParentStudent ps = new ParentStudent();
        ps.setParentId(pId);
        ps.setStudentId(stuId);
        parentStudentRepository.save(ps);

        //学生角色集合
        List<StudentRole> srList = new ArrayList<StudentRole>();


        logger.info("开始循环学生信息");
        //循环教师的角色
        for (int i = 0; i < studentRoleList.size(); i++) {
            logger.info("学生角色信息 = {}", studentRoleList);
            //拆分信息
            String[] classAndRole = studentRoleList.get(i).split("-");
            //如果没有班级
            if (classAndRole.length == 1) {
                continue;
            }

            //创建角色
            StudentRole sr = new StudentRole();
            sr.setClassId(Integer.valueOf(classMap.get(classAndRole[0]).toString().trim()));
            sr.setRoleId(Integer.valueOf(roleMap.get(classAndRole[1]).toString().trim()));
            sr.setStudentId(stuId);
            srList.add(sr);
        }

        studentRoleRepository.saveAll(srList);

        return new Result();
    }

    @Override
//    @Transactional
    public Result batchAdditionStudent(String schoolId, String student) {


        logger.info("批量添加学生");

        //查询这个学校有的角色
        String sql = "select name,id from `roles` WHERE `school_id` = ? and `type` = 2";
        logger.info("询这个学校有的角色 SQL ={}", sql);
        Map<String, Object> roleMap = studentJdbc.queryIdMap(sql, "name", "id", schoolId);
        logger.info("学校有的角色map大小 = {}", roleMap.size());

        //查询这个学校的班级
        sql = "select c.id,c.`name`   from `classes` c WHERE c.`school_id`  = ?";
        logger.info("查询这个学校的班级 SQL ={}", sql);
        Map<String, Object> classMap = studentJdbc.queryIdMap(sql, "name", "id", schoolId);
        logger.info("班级map大小 = {}", classMap.size());


        //查询学校状态 0测试学校，1正式学校
        sql = "select type string from school where id = ?";
        String type = studentJdbc.queryString(sql, schoolId);

        sql = "select phone,`id` from student";
        Map<String, String> phoneMap = studentJdbc.getMap(sql, "phone", "id");
        sql = "select card_number,`id` from student where `school_id`  = " + schoolId;
        Map<String, String> cardMap = studentJdbc.getMap(sql, "card_number", "id");


        //学生角色集合
        List<StudentRole> srList = new ArrayList<StudentRole>();
        //学生跟家长的关联集合
        List<ParentStudent> psList = new ArrayList<ParentStudent>();
        //同步到海威达的数据
        List<Student> hwdlist = new ArrayList<>();
        //把信息转为list集合
        List<Map<String, Object>> studentList = (List<Map<String, Object>>) JSON.parse(student);
        //循环每个学生的信息
        for (Map<String, Object> map : studentList) {
            logger.info("学生信息 = {}", map.toString());
            //学生名称
            String name = map.get("name").toString().trim();
            //学生手机号码
            String phone = map.get("phone").toString().trim();
            //获取学生性别
            String sex = map.get("sex").toString().trim();
            //获取学号
            String stuNo = map.get("stuNo").toString().trim();
            //获取卡号
            String cardNumber = map.get("cardNumber").toString().trim();
            //卡号长度9前面补0
            if (cardNumber.length() == 9) {
                cardNumber = "0" + cardNumber;
            }


            int verify = verifyPhone(phone, type, phoneMap);

            if (verify == 1) {
                return new Result(CodeUtil.PLEASE_ENTER_THE_CORRECT_MOBILE_PHONE_NUMBER.getCode(), "号码:" + phone + "不符合要求，请输出正确号码");
            } else if (verify == 2) {
                return new Result(CodeUtil.MOBILE_NUMBER_ALREADY_EXISTS.getCode(), "号码:" + phone + CodeUtil.MOBILE_NUMBER_ALREADY_EXISTS.getMsg());
            }


            //卡号验证结果
            int verifyCard = verifyCard(cardNumber, cardMap, null);
            if (verifyCard == 1) {
                return new Result(CodeUtil.THE_CARD_NUMBER_TO_REPEAT);
            }

            //学生角色
            List<String> teacherRole = (List<String>) JSON.parse(map.get("list").toString());


            //创建学生集合
            Student s = new Student();
            s.setSchoolId(Integer.valueOf(schoolId));
            s.setCardNumber(cardNumber);
            s.setPhone(phone);
            s.setClassId(Integer.valueOf(classMap.get(teacherRole.get(0).split("-")[0]).toString()));
            s.setSex("男".equals(sex) ? 0 : 1);
            s.setHeadPortrait("男".equals(sex) ? "https://oss.yuguozhijia.online/headPortrait/学生头像（男）.png" : "https://oss.yuguozhijia.online/headPortrait/学生头像（女）.png");
            s.setStuNo(stuNo);
            s.setStudentName(name);
            s.setState(0);
            s.setAdmissionTime(new Date());

            Integer stuId = studentRepository.save(s).getId();
            hwdlist.add(s);
            logger.info("学生id = {}", stuId);

            //创建家长信息
            Parent p = new Parent();
            p.setName(name + "家长");
            p.setPassword("x16YjoF1LNE=");
            p.setPhone(phone);
            Integer pId = parentRepository.save(p).getId();
            logger.info("家长id");

            //创建家长跟学生关联的实体
            ParentStudent ps = new ParentStudent();
            ps.setParentId(pId);
            ps.setStudentId(stuId);
            psList.add(ps);


            logger.info("开始循环学生信息");
            //循环教师的角色
            for (int i = 0; i < teacherRole.size(); i++) {
                logger.info("学生角色信息 = {}", teacherRole);
                //拆分信息
                String[] classAndRole = teacherRole.get(i).split("-");
                //只有长度大于1才进来，
                if (classAndRole.length > 1) {
                    //创建角色
                    StudentRole sr = new StudentRole();
                    sr.setClassId(Integer.valueOf(classMap.get(classAndRole[0]).toString().trim()));
                    sr.setRoleId(Integer.valueOf(roleMap.get(classAndRole[1]).toString().trim()));
                    sr.setStudentId(stuId);
                    srList.add(sr);
                }


            }
        }
        //添加学生跟家长的关联信息
        parentStudentRepository.saveAll(psList);
        //添加学生角色
        studentRoleRepository.saveAll(srList);

        try {
            haiweidaCooperativeSchoolJdbc.haiweidaStudent(hwdlist);
        } catch (Exception e) {
            logger.info("海威达同步学生失败 e", e);
        }

        return new Result();
    }

    @Override
    public Result resetAllStudentPwd(String schoolId, String passWord) {

        logger.info("重置全校的学生密码");
        String sql = "UPDATE `parent` SET `password` = '" + MyDES.encryptBasedDes(passWord) + "' WHERE " +
                "`id` in(select ps.parent_id  from parent_student ps  Left JOIN student s on (s.id=ps.student_id) where s.school_id=?) ";
        logger.info("修改密码sql = {}", sql);

        studentJdbc.updateByParameter(sql, schoolId);

        return new Result();
    }

    @Override
    public Result resetStudentPwd(Integer id, String passWord) {
        logger.info("重置学生密码");
        String sql = "UPDATE `parent` SET `password` = '" + MyDES.encryptBasedDes(passWord) + "' WHERE " +
                "`id` in(select ps.parent_id  from parent_student ps  Left JOIN student s on (s.id=ps.student_id) where s.id=?) ";
        logger.info("修改密码sql = {}", sql);

        studentJdbc.updateByParameter(sql, id);

        return new Result();
    }

    @Override
    public Result updateStudent(Student student) {

        //循环每个学生的角色
        List<String> studentRoleList = (List<String>) JSON.parse(student.getList());

        //查询这个学校有的角色
        String sql = "select name,id from `roles` WHERE `school_id` = ? and `type` = 2";
        logger.info("询这个学校有的角色 SQL ={}", sql);
        Map<String, Object> roleMap = studentJdbc.queryIdMap(sql, "name", "id", student.getSchoolId());
        logger.info("学校有的角色map大小 = {}", roleMap.size());

        //查询这个学校的班级
        sql = "select c.id,c.`name`    from `classes` c  WHERE c.`school_id`  = ?";
        logger.info("查询这个学校的班级 SQL ={}", sql);
        Map<String, Object> classMap = studentJdbc.queryIdMap(sql, "name", "id", student.getSchoolId());
        logger.info("班级map大小 = {}", classMap.size());
        student.setClassId(Integer.valueOf(classMap.get(studentRoleList.get(0).split("-")[0]).toString().trim()));

        //创建修改工具
        Optional<Student> optional = studentRepository.findById(student.getId());
        //判断头像是否为空
        if (StringUtils.isEmpty(student.getHeadPortrait())) {
            //头像
            student.setHeadPortrait(student.getSex() == 0 ? "https://oss.yuguozhijia.online/headPortrait/学生头像（男）.png" : "https://oss.yuguozhijia.online/headPortrait/学生头像（女）.png");
        }
        UpdateTool.copyNullProperties(optional.get(), student);

        //验证结果
        int verify = verifyPhone(student);
        if (verify == 1) {
            return new Result(CodeUtil.PLEASE_ENTER_THE_CORRECT_MOBILE_PHONE_NUMBER);
        } else if (verify == 2) {
            return new Result(CodeUtil.MOBILE_NUMBER_ALREADY_EXISTS);
        }

        sql = "select card_number,`id` from student where `school_id`  = " + student.getSchoolId();
        Map<String, String> phoneMap = studentJdbc.getMap(sql, "card_number", "id");

        //卡号验证结果
        int verifyCard = verifyCard(student.getCardNumber(), phoneMap, student.getId() + "");
        if (verifyCard == 1) {
            return new Result(CodeUtil.THE_CARD_NUMBER_TO_REPEAT);
        }


        //先删除学生已有的学生角色
        sql = "DELETE FROM `student_role` WHERE `student_id` = ? ";
        logger.info("删除学生的角色 sql ={}", sql);
        studentJdbc.updateByParameter(sql, student.getId());
        try {
            //海威达
            List<Student> hwdlist = new ArrayList<>();
            hwdlist.add(student);
            haiweidaCooperativeSchoolJdbc.haiweidaStudent(hwdlist);
        } catch (Exception e) {
            logger.info("海威达同步学生失败 e", e);
        }

        //学生角色集合
        List<StudentRole> srList = new ArrayList<StudentRole>();


        logger.info("开始循环学生角色信息");
        //循环教师的角色
        for (int i = 0; i < studentRoleList.size(); i++) {
            logger.info("学生角色信息 = {}", studentRoleList);
            //拆分信息
            String[] classAndRole = studentRoleList.get(i).split("-");

            //长度为1是跳过
            if (classAndRole.length == 1) {
                continue;
            }
            //创建角色
            StudentRole sr = new StudentRole();
            sr.setClassId(Integer.valueOf(classMap.get(classAndRole[0]).toString().trim()));
            sr.setRoleId(Integer.valueOf(roleMap.get(classAndRole[1]).toString().trim()));
            sr.setStudentId(student.getId());
            srList.add(sr);
        }

        studentRoleRepository.saveAll(srList);
        //修改数据
        studentRepository.save(student);
        sql = "UPDATE `parent` SET `phone` = ? WHERE `id` = " +
                "(select parent_id FROM parent_student where student_id = ? LIMIT 1)";
        studentJdbc.updateByParameter(sql, student.getPhone(), student.getId());
        return new Result();
    }


    @Override
    public Result testStudentData(String schoolId, String student) {

        logger.info("检测学生信息");

        //查询这个学校有的角色
        String sql = "select name,id from `roles` WHERE `school_id` = ? and `type` = 2";
        logger.info("询这个学校有的角色 SQL ={}", sql);
        Map<String, Object> roleMap = studentJdbc.queryIdMap(sql, "name", "id", schoolId);
        logger.info("学校有的角色map大小 = {}", roleMap.size());

        //查询这个学校的班级
        sql = "select c.id,c.`name`  from `classes` c  WHERE c.`school_id`  = ?";
        logger.info("查询这个学校的班级 SQL ={}", sql);
        Map<String, Object> classMap = studentJdbc.queryIdMap(sql, "name", "id", schoolId);
        logger.info("班级map大小 = {}", classMap.size());

        List<String> errorList = new ArrayList<String>();
        //错误坐标
        List<String> errorCoordinate = new ArrayList<>();

        //作物的x坐标
        int errorX = 0;

        //把信息转为list集合
        List<Map<String, Object>> studentList = (List<Map<String, Object>>) JSON.parse(student);


        //检测重复号码
        List<String> stringPhone = new ArrayList<>();

        List<String> stringCard = new ArrayList<>();


        for (Map<String, Object> map : studentList) {//数据过滤
            stringPhone.add(map.get("phone") + "");
            stringCard.add(map.get("cardNumber") + "");
        }

        Set<String> collectP = stringPhone.stream().filter(i -> stringPhone.stream().filter(i::equals).count() > 1).collect(Collectors.toSet());


        Set<String> collectK = stringCard.stream().filter(i -> stringCard.stream().filter(i::equals).count() > 1).collect(Collectors.toSet());


        if (collectP.size() > 0) {//手机号码有重复
            return new Result(CodeUtil.NUMBER_ALREADY_EXISTS.getCode(), "添加的号码:" + collectP.toString() + CodeUtil.NUMBER_ALREADY_EXISTS.getMsg());
        }

        collectK.removeAll(Collections.singleton(null));//去重null
        collectK.removeAll(Collections.singleton(""));//去重''
        if (collectK.size() > 0) {//卡号有重复
            return new Result(CodeUtil.THE_CARD_NUMBER_TO_REPEAT.getCode(), "添加的卡号:" + collectK.toString() + CodeUtil.THE_CARD_NUMBER_TO_REPEAT.getMsg());
        }

        //查询学校状态 0测试学校，1正式学校
        sql = "select type string from school where id = ?";
        String type = studentJdbc.queryString(sql, schoolId);

        sql = "select phone,`id` from student";
        Map<String, String> phoneMap = studentJdbc.getMap(sql, "phone", "id");

        sql = "select card_number,`id` from student where `school_id`  = " + schoolId;
        Map<String, String> cardMap = studentJdbc.getMap(sql, "card_number", "id");

        //循环每个学生的信息
        for (Map<String, Object> map : studentList) {
            logger.info("学生信息 = {}", map.toString());
            //性别
            String sex = map.get("sex").toString();
            //学生名称
            String name = map.get("name").toString();
            //判断如果不为男女就退出
            if (!("男".equals(sex) || "女".equals(sex))) {
                logger.info("性别输入错误");
                errorList.add(name + ":学生的性别输入错误（男/女）");
                errorCoordinate.add(errorX + ":1");
                continue;
            }
            //学生名称
            String phone = map.get("phone").toString();
            Student s = new Student();
            s.setPhone(phone);
            s.setSchoolId(Integer.valueOf(schoolId));
            int verify = verifyPhoneTwo(s, type, phoneMap);
            //结果
            if (verify == 1) {
                errorList.add("号码不正确");
                errorCoordinate.add(errorX + ":4");
            } else if (verify == 2) {
                errorList.add("号码在该系统中已存在");
                errorCoordinate.add(errorX + ":4");
            }
            int cardNumber = verifyCard(map.get("cardNumber") + "", cardMap, null);

            if (cardNumber == 1) {
                errorList.add("卡号在该学校中已存在");
                errorCoordinate.add(errorX + ":2");
            }

            //学生角色
            List<String> teacherRole = (List<String>) JSON.parse(map.get("list").toString());


            logger.info("开始循环学生信息");
            //循环教师的角色
            for (int i = 0; i < teacherRole.size(); i++) {
                logger.info("学生角色信息 = {}", teacherRole);
                //拆分信息
                String[] classAndRole = teacherRole.get(i).split("-");
                //判断班级是否存在
                if (StringUtils.isEmpty(classMap.get(classAndRole[0]))) {
                    logger.info("班级输入错误");
                    errorList.add(name + ":学生的角色班级数据错误（" + classAndRole[0] + "）");
                    errorCoordinate.add(errorX + ":5");
                }
                //如果长度大于1
                if (classAndRole.length > 1) {
                    //判断角色是否存在
                    if (StringUtils.isEmpty(roleMap.get(classAndRole[1]))) {
                        logger.info("班级输入错误");
                        errorList.add(name + ":学生的角色错误（" + classAndRole[1] + "）");
                        errorCoordinate.add(errorX + ":5");
                    }
                }


            }
            errorX++;
        }

        //判断错误信息大于0
        if (errorList.size() > 0) {
            Map<String, Object> map = new HashMap<>();
            map.put("errorList", errorList);
            map.put("errorCoordinate", errorCoordinate);
            logger.info("有错误信息");
            return new Result(CodeUtil.RETRIEVE_AND_REPORT_ERRORS, map);
        }

        return new Result();
    }

    @Override
    public void exportStudentTemplate(RequestParameterWrapper request, HttpServletResponse response) {
        logger.info("导出学生模板");
        ExeclUtils.downloadExcelOrdinaryTemplate(request, response, "template/studentTemplates.xls", "学生导入模板.xls");
    }


    @Override
    public void exportStudent(RequestParameterWrapper request, HttpServletResponse response, Integer classId) {

        //学校id
        String schoolId = request.getParam("schoolId");
        //查询学生sql
        String sql = "select s.student_name,c.`name` class_name,case s.sex when '0' then '男' else '女' end as sex ,s.stu_no,s.phone,s.card_number from student s LEFT JOIN classes c on(s.class_id=c.id)  where   s.school_id = ?  and s.class_id = ?";

        //查询学生信息
        List<Map<String, Object>> list = studentJdbc.queryListMap(sql, schoolId, classId);

        //key
        String[] key = new String[]{"学生名字", "班级名称", "性别", "学号", "手机号码", "卡号"};
        //values
        String[] value = new String[]{"student_name", "class_name", "sex", "stu_no", "phone", "card_number"};

        try {
            ExeclUtils.downloadExcelMapTemplate(response, list, key, value, "学生信息");
        } catch (IOException e) {
            logger.info("学生导出错误,e", e);
        }

    }

    @Override
    public Result findJurisdictionStudent(String teacherId, String schoolId) {

        //查询教师的数据权限
        int type = classesJdbc.findDatePermissions(teacherId);
        //查询教师的年级
        List<Map<String, Object>> list = classesJdbc.queruGradeList(teacherId, type, schoolId);

        for (Map<String, Object> gradeMap : list) {
            //查询教师的班级
            List<Map<String, Object>> classList = classesJdbc.queryClassList(String.valueOf(gradeMap.get("id")), type, schoolId, teacherId);

            gradeMap.put("list", classList);
        }
        return new Result(list);

    }

    @Override
    public Result manualSynchronization(String schoolId) {

        String fileName = "F://sssss.json";
        File file = new File(fileName);

        try {
            if (!file.exists()) {
                System.out.println("file not exist");
            }

            FileReader fileReader = new FileReader(file);
            BufferedReader bReader = new BufferedReader(fileReader);

            String s = "";
            String tempString = null;
            while ((tempString = bReader.readLine()) != null) {
                s += tempString;
            }

            System.err.println(s);


            //查询这个学校是否是合作学校
            String sql = "select id,school_code,user_name,password,school_id,token,exp_time,uuid" +
                    " from haiweida_cooperative_school where school_id = ?";
            Map<String, Object> map = classesJdbc.queryMap(sql, schoolId);


            //获取时间
            Date date = DateUtil.strToDateLong(map.get("exp_time").toString(), "yyyy-MM-dd HH:mm:ss");
            //判断有效时间小于当前时间
            if (date.getTime() < System.currentTimeMillis()) {
                //刷新token
                Map<String, Object> tokenMap = haiweidaCooperativeSchoolJdbc.tokenMapTwo(map);
                if (!StringUtils.isEmpty(tokenMap)) {
                    map.put("token", tokenMap.get("token").toString());
                }

            }


            String ss =HaiweidaUtil.userSynchronization(JSON.toJSON(s).toString(), map.get("token").toString());

            //转成json
            JSONObject josn = JSON.parseObject(ss);
            //把结果添加到数据库
            haiweidaCooperativeSchoolJdbc.inserthaiweidaSynLog(josn, "user", "", Integer.valueOf(schoolId), "0");
            bReader.close();
            fileReader.close();
        } catch (IOException e) {
            logger.error("e", e);
        }

        return new Result();
    }

    @Override
    public Result ManualFaceSynchronization(String schoolId) {
        String fileName = "F://人脸.json";
        File file = new File(fileName);

        try {
            if (!file.exists()) {
                System.out.println("file not exist");
            }

            FileReader fileReader = new FileReader(file);
            BufferedReader bReader = new BufferedReader(fileReader);

            String s = "";
            String tempString = null;
            while ((tempString = bReader.readLine()) != null) {
                s += tempString;
            }

            System.err.println(s);


            //查询这个学校是否是合作学校
            String sql = "select id,school_code,user_name,password,school_id,token,exp_time,uuid" +
                    " from haiweida_cooperative_school where school_id = ?";
            Map<String, Object> map = classesJdbc.queryMap(sql, schoolId);

            //获取时间
            Date date = DateUtil.strToDateLong(map.get("exp_time").toString(), "yyyy-MM-dd HH:mm:ss");
            //判断有效时间小于当前时间
            if (date.getTime() < System.currentTimeMillis()) {
                //刷新token
                Map<String, Object> tokenMap = haiweidaCooperativeSchoolJdbc.tokenMapTwo(map);
                if (!StringUtils.isEmpty(tokenMap)) {
                    map.put("token", tokenMap.get("token").toString());
                }

            }


            String ss = HaiweidaUtil.faceSynchronization(JSON.toJSON(s).toString(), map.get("token").toString());
            //转成json
            JSONObject josn = JSON.parseObject(ss);
            //把结果添加到数据库
            haiweidaCooperativeSchoolJdbc.inserthaiweidaSynLog(josn, "photo", "", Integer.valueOf(schoolId), "0");
            bReader.close();
            fileReader.close();
        } catch (IOException e) {
            logger.error("e", e);
        }

        return new Result();
    }


    public int verifyCard(String card, Map<String, String> cardMap, String id) {
        if (!StringUtils.isEmpty(card) && !StringUtils.isEmpty(cardMap) && cardMap.get(card) != null && !cardMap.get(card).equals(id)) {
            //卡号已存在
            return 1;
        }
        return 0;

    }


    public int verifyPhoneTwo(Student student, String type, Map<String, String> phoneMap) {

        //验证手机号码
        //如果为测试学校
        if ("0".equals(type)) {
            //测试账号必须为11位数
            if (student.getPhone().length() != 11) {
                //请输入正确的手机号码
                return 1;
            }
        } else {
            //2020手机号码验证
            String regex = "^((13[0-9])|(14[0|5|6|7|9])|(15[0-3])|(15[5-9])|(16[6|7])|(17[2|3|5|6|7|8])|(18[0-9])|(19[1|8|9]))\\d{8}$";
            if (student.getPhone().length() != 11) {
                System.out.println("手机号应为11位数");
                return 1;
            }
//            else {
////                Pattern p = Pattern.compile(regex);
////                Matcher m = p.matcher(student.getPhone());
////                boolean isMatch = m.matches();
////                if (!isMatch) {
////                    //请输入正确的手机号码
////                    return 1;
////
////                }
////            }
        }

        //判断手机号码是否已经存在
        Integer id = student.getId();
        String phone = student.getPhone();
//        if (!StringUtils.isEmpty(phoneMap) && phoneMap.containsKey(phone) && !id.toString().equals(phoneMap.get(phone))) {
//            //号码已存在
//            return 2;
//        }
        if (!StringUtils.isEmpty(phoneMap) && phoneMap.get(phone) != null && !phoneMap.get(phone).equals(id + "")) {
            //号码已存在
            return 2;
        }
        //无误
        return 0;
    }

    /**
     * 验证手机号码
     *
     * @param student
     * @return
     */
    public int verifyPhone(Student student) {
        //查询学校状态 0测试学校，1正式学校
        String sql = "select type string from school where id = ?";
        String type = studentJdbc.queryString(sql, student.getSchoolId());

        //验证手机号码
        //如果为测试学校
        if ("0".equals(type)) {
            //测试账号必须为11位数
            if (student.getPhone().length() != 11) {
                //请输入正确的手机号码
                return 1;
            }
        } else {
            //2020手机号码验证
            String regex = "^((13[0-9])|(14[0|5|6|7|9])|(15[0-3])|(15[5-9])|(16[6|7])|(17[2|3|5|6|7|8])|(18[0-9])|(19[1|8|9]))\\d{8}$";
            if (student.getPhone().length() != 11) {
                System.out.println("手机号应为11位数");
                return 1;
            }
//            else {
//                Pattern p = Pattern.compile(regex);
//                Matcher m = p.matcher(student.getPhone());
//                boolean isMatch = m.matches();
//                if (!isMatch) {
//                    //请输入正确的手机号码
//                    return 1;
//
//                }
//            }
        }

        sql = "select phone,`id` from student";
        Map<String, String> phoneMap = studentJdbc.getMap(sql, "phone", "id");
        //判断手机号码是否已经存在
        Integer id = student.getId();
        String phone = student.getPhone();
//        if (!StringUtils.isEmpty(phoneMap) && phoneMap.containsKey(phone) && !id.toString().equals(phoneMap.get(phone))) {
//            //号码已存在
//            return 2;
//        }
        if (!StringUtils.isEmpty(phoneMap) && phoneMap.get(phone) != null && !phoneMap.get(phone).equals(id + "")) {
            //号码已存在
            return 2;
        }
        //无误
        return 0;
    }


    /**
     * 验证卡号
     *
     * @param
     * @return
     */
    public int verifyPhone(String phone, String type, Map<String, String> phoneMap) {

        //验证手机号码
        //如果为测试学校
        if ("0".equals(type)) {
            //测试账号必须为11位数
            if (phone.length() != 11) {
                //请输入正确的手机号码
                return 1;
            }
        } else {
            //2020手机号码验证
            String regex = "^((13[0-9])|(14[0|5|6|7|9])|(15[0-3])|(15[5-9])|(16[6|7])|(17[2|3|5|6|7|8])|(18[0-9])|(19[1|8|9]))\\d{8}$";
            if (phone.length() != 11) {
                System.out.println("手机号应为11位数");
                return 1;
            }
//            else {
//                Pattern p = Pattern.compile(regex);
//                Matcher m = p.matcher(phone);
//                boolean isMatch = m.matches();
//                if (!isMatch) {
//                    //请输入正确的手机号码
//                    return 1;
//
//                }
//            }
        }

        //判断手机号码是否已经存在
        //Integer id = student.getId();
        // String phone = student.getPhone();
//        if (!StringUtils.isEmpty(phoneMap) && phoneMap.containsKey(phone) && !id.toString().equals(phoneMap.get(phone))) {
//            //号码已存在
//            return 2;
//        }
        if (!StringUtils.isEmpty(phoneMap) && phoneMap.get(phone) != null) {
            //号码已存在
            return 2;
        }
        //无误
        return 0;
    }


}
